﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using models;
using System.Data.SqlClient;
using Models;

namespace DAL
{
  public  class OrderService
    {
        //查询订单信息表
        public SqlDataReader selectOrder()
        {
            string sql = string.Format("select * from order");
            return DBhelper.DataReader(sql);
        }
        //根据id查询订单信息表
        public SqlDataReader selectOrderid(int id)
        {
            string sql = string.Format("select * from order where Id='{0}'",id);
            return DBhelper.DataReader(sql);
        }
        //删除订单
        public bool DeleteOrder(int id)
        {
            string sql = string.Format("delete order where Id='{0}'", id);
            return DBhelper.ex(sql);
        }
        //新增订单
        public bool InsertOrder(Order order) 
        {
            string sql = string.Format("insert [order] values('{0}','{1}','{2}','{3}')",order.Orders,order.DateNow,order.UserID,order.State);
            return DBhelper.ex(sql);
        }
      
        //查询订单详细信息
        public SqlDataReader selectorders(int id)
        {
            string sql = string.Format(@"select o.Orders,o.DateNow,r.astate from [order] o 
                                                      join [User] u on o.UserID = u.Id        
                                                                        join ater r on o.State = r.id
                                                                      
                                                                where u.Id='{0}'", id);
            return DBhelper.DataReader(sql);




            //@"select  o.Orders,p.Pic, o.DateNow, p.ProName, p.Speci, p.Price, c.Count,o.State
            //                              from
            //                             [User] u join [order] o
            //                              on  o.UserID = u.Id  join cart c
            //                                on c.UserId = u.Id join product p
            //                                 on c.ProID = p.Id join pay y on o.Orders=y.Batch
            //                                where u.Id='{0}' ", id
        }
        //根据订单状态查询订单数量
        public SqlDataReader selectordersstate(int id, int state)
        {
            string sql = string.Format(@"select o.Orders,o.DateNow,a.astate  from [order] o join ater a on o.State = a.id   where State='{1}' and o.UserID='{0}' ", id, state);
            return DBhelper.DataReader(sql);
        }

        //根据订单号删除订单详细信息
        public bool DeleteOrderss(string order)
        {
            string sql = string.Format("delete [order] where Orders='{0}'",order);
            return DBhelper.ex(sql);
        }

        //根据订单号查询商品详细信息
        public SqlDataReader selectorde(string order)
        {
            string sql = string.Format(@"select  o.Orders,p.thumbnail, o.DateNow, p.ProName, p.Speci, p.Price, a.Count,o.State,a.Sum,r.astate
                                          from
                                         [User] u join [order] o
                                          on  o.UserID = u.Id  
                                            join pay a on o.Orders=a.Batch
											 join product p
                                             on a.ProID = p.Id 
                                                  join ater r on o.State = r.id
                                            where o.Orders='{0}' ", order);
            return DBhelper.DataReader(sql);

        }
        //通过订单号修改订单为已付款
        public bool UporderByIdIsState(string ddh)
        {
            string sql = string.Format("update [order] set state=0 where orders='{0}'", ddh);
            return DBhelper.ex(sql);
        }
        //通过订单号修改为已收货
        public bool updataByidState(string orders)
        {
            string sql = string.Format("update [order] set state=0 where orders='{0}'", orders);
            return DBhelper.ex(sql);
        }


        //修改订单状态
        public bool updateorder(int id, Order order)
        {
            string sql = string.Format("update Order ser state='{1}' where id='{0}'",id,order.State);
            return DBhelper.ex(sql);
        }

        //查询商品总价
        public SqlDataReader selectsum(string order)
        {
            string sql = string.Format(" select sum([SUM]) from pay where Batch = '{0}'",order);
            return DBhelper.DataReader(sql); 
        }
    }
}
